package cn.zptc.dao.impl;

import cn.zptc.dao.FavoriteDao;
import cn.zptc.entity.Favorite;
import cn.zptc.entity.FavoriteCategory;
import cn.zptc.utils.C3p0Util;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class FavoriteDaoImpl implements FavoriteDao {
    /**
     * 根据用户的id查询用户的链接分类
     *
     * @param userId
     * @return 某个用户的链接分类
     */
    @Override
    public List<FavoriteCategory> selectMyFavoriteCategorys(int userId) throws SQLException {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        String sql="select tfc.id,tfc.category_name categoryName" +
                " from t_user_favorite_category tufc join t_favorite_category tfc" +
                " on tufc.favorite_category_id=tfc.id\n" +
                "    where tufc.user_id=?";
        return runner.query(sql, new BeanListHandler<FavoriteCategory>(FavoriteCategory.class), userId);
    }

    /**
     *
     * @param favorite
     * @return
     */
    @Override
    public int insert(Favorite favorite) throws SQLException {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "INSERT INTO `t_favorite` (`link_id`, `favorite_name`, `category_id`, `user_id`, `login_name`, `password`) VALUES (?,?,?,?,?,?)";
        //todo:用户的id要修改
        return runner.insert(sql, rs -> rs.next() ? rs.getInt(1) : -1,
                favorite.getLinkId(), favorite.getFavoriteName(), favorite.getCategoryId(), 1, favorite.getLoginName(), favorite.getPassword());
    }

    /**
     * 查询出我的收藏
     *
     * @param userId
     * @return
     */
    @Override
    public List<Favorite> selectMyFavorites(int userId) throws SQLException {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "SELECT\n" +
                "  f.`id`,\n" +
                "  f.`link_id` linkId,\n" +
                "  f.`favorite_name` favoriteName,\n" +
                "  f.`category_id` categoryId,\n" +
                "  f.`login_name` loginName,\n" +
                "  f.`password` password,\n" +
                "   l.`link_url` linkUrl\n" +
                "FROM\n" +
                "  `t_favorite` f left join `t_link` l on f.`link_id` = l.`id`\n" +
                "WHERE f.user_id = ?\n" +
                "order by f.`category_id`, f.`id`";

        return runner.query(sql,new BeanListHandler<Favorite>(Favorite.class),userId);
    }

    /**
     * 查询用户在收藏中存在的分类
     *
     * @param userId
     * @return
     */
    @Override
    public List<FavoriteCategory> selectMyLinkCategorysInFavorite(int userId) throws SQLException {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "SELECT\n" +
                "  f.`category_id` id,\n"+
                "  c.`category_name` categoryName\n"+
                "FROM\n" +
                "  `t_favorite` f left join `t_favorite_category` c\n" +
                "on f.`category_id` = c.`id`\n"+
                "WHERE f.user_id = ?\n"+
                "GROUP BY f.`category_id`";

        return runner.query(sql,new BeanListHandler<FavoriteCategory>(FavoriteCategory.class),userId);
    }

    /**
     * 删除一个收藏
     *
     * @param favoriteId
     * @param userId
     * @return
     */
    @Override
    public int deleteFavorite(int favoriteId, int userId) throws SQLException {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        String sql="delete from `t_favorite` where id = ? and user_id = ?";
        return runner.update(sql,favoriteId,userId);
    }

    /**
     * 获取我的一个收藏
     * 防止别人来操作，所以查询时需要核对userId
     *
     * @param favoriteId
     * @param userId
     * @return
     */
    @Override
    public Favorite getFavorite(int favoriteId, int userId) throws SQLException {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        String sql="select f.id,f.favorite_name as favoriteName,f.category_id as categoryId," +
                " f.login_name as loginName,f.password,l.link_url as linkUrl  " +
                " from `t_favorite` f left join `t_link` l on f.link_id = l.id " +
                " where f.id = ? and f.user_id = ?";
        return runner.query(sql,new BeanHandler<Favorite>(Favorite.class),favoriteId,userId);
    }
}
